﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Fcdbas.WinUI.Common
{
    public class Query
    {
        public Query()
        {

        }

        public string OrderByBuilder(List<QueryItem> ItemList)
        {
            StringBuilder condition = new StringBuilder("");
            var list = (from qi in ItemList
                        where !string.IsNullOrEmpty(qi.OrderBy)
                        select qi).OrderBy(p => p.OrderSeq);
            //if (list.Count() > 0)
            //    condition.Append("Order by ");
            foreach (QueryItem d in list)
            {
                string order = " ASC";
                if(d.OrderBy == "升序")
                    order = " ASC";
                else
                    order = " DESC";
                condition.Append(d.FieldName + order);
                condition.Append(",");
            }
            if (condition.Length > 1)
                condition.Remove(condition.Length - 1, 1);
            return condition.ToString();
        }
        /// <summary>
        /// 生成查詢條件
        /// </summary>
        /// <param name="ItemList"></param>
        /// <returns></returns>
        public string ConditionBuilder(IList<QueryItem> ItemList,bool bUCase)
        {
            StringBuilder condition = new StringBuilder("");
            foreach(QueryItem item in ItemList.Where(p=>!string.IsNullOrEmpty(p.Value)).ToList())
            {
                //condition.Append(string.Format("{0} {1} {2} '{3}' {4} {5} ",
                //    string.IsNullOrEmpty(item.LeftParenthesis) ? "" : item.LeftParenthesis,
                //    item.FieldName,
                //    item.ExpressionSymbol,
                //    item.ExpressionSymbol=="like"?"%"+item.FieldValue+"%":item.FieldValue,
                //    string.IsNullOrEmpty(item.RightParenthesis) ? "" : item.RightParenthesis,
                //    string.IsNullOrEmpty(item.WithOr) ? "" : item.WithOr
                //    ));
                string where = GetCondition(item, bUCase);
                if (where == "ERROR")
                    return where;
                else
                    condition.Append("("+ where + ")");
                condition.Append(" and ");
            }
            if(condition.Length > 5)
                condition.Remove(condition.Length - 5, 5);
            return condition.ToString();
        }
        private static readonly string Oper = "%,!,>,<,>=,<=,!=,..,;";
        private string GetCondition(QueryItem qi,bool bUCase)
        {
            string value = "";
            int idx = -1;
            string []op = Oper.Split(',');
            foreach (string s in op)
            {
                idx = qi.Value.IndexOf(s);
                if (idx >= 0)
                {
                    if (s == "%")
                        value = qi.Value;
                    else
                        value = qi.Value.Substring(idx + 1);
                    return BuildCondition(qi, value, s, bUCase);
                }
               
            }
            return BuildCondition(qi, qi.Value, "=", bUCase);
            
        }
        private string BuildCondition(QueryItem qi, string value,string oper,bool bUCase)
        {
            if (qi.DataType == typeof(string))
            {
                switch (oper)
                {
                    case "=":

                        if (!bUCase)
                            return "ucase(" + qi.FieldName + ")=ucase('" + value + "')";
                        else
                            return qi.FieldName + " ='" + value + "'";
                        
                    case "%":
                        return qi.FieldName + " LIKE '" + value + "'";
                    case "!":
                        return qi.FieldName + " IS NULL";
                    case ">":
                        return qi.FieldName  + " >'" + value + "'";
                    case "<":
                        return qi.FieldName + " <'" + value + "'";
                    case ">=":
                        return qi.FieldName + " >='" + value + "'";
                    case "<=":
                        return qi.FieldName + " <='" + value + "'";
                    case "!=":
                        return qi.FieldName + " !='" + value + "'";
                    case "..":
                        {
                            string[] s1 = qi.Value.Split("..");
                            if (s1.Length >= 2)
                            {
                                return qi.FieldName + " BETWEEN '" + s1[0] + "' AND '" + s1[1] + "'";
                            }
                            else
                                return "ERROR";
                        }
                    case ";":
                        {
                            string[] s2 = qi.Value.Split(";");
                            string where = qi.FieldName + " IN ('";
                            foreach (string s in s2)
                            {
                                where += s + "','";
                            }
                            where = where.Remove(where.Length - 2);
                            where += ")";
                            return where;
                        }
                }

            }
            if (qi.DataType == typeof(decimal) || qi.DataType == typeof(int) || qi.DataType == typeof(float))
            {
                switch (oper)
                {
                    case "=":

                       return qi.FieldName + " =" + value + "";

                    case "%":
                        return qi.FieldName + " LIKE '" + value + "%'";
                    case "!":
                        return qi.FieldName + " IS NULL";
                    case ">":
                        return qi.FieldName + " >" + value ;
                    case "<":
                        return qi.FieldName + " <" + value ;
                    case ">=":
                        return qi.FieldName + " >=" + value ;
                    case "<=":
                        return qi.FieldName + " <=" + value ;
                    case "!=":
                        return qi.FieldName + " !=" + value ;
                    case "..":
                        {
                            string[] s1 = qi.Value.Split("..");
                            if (s1.Length >= 2)
                            {
                                return qi.FieldName + " BETWEEN " + s1[0] + " AND " + s1[1] ;
                            }
                            else
                                return "ERROR";
                        }
                    case ";":
                        {
                            string[] s2 = qi.Value.Split(";");
                            string where = qi.FieldName + " IN (";
                            foreach (string s in s2)
                            {
                                where += s + ",";
                            }
                            where = where.Remove(where.Length - 1);
                            where += ")";
                            return where;
                        }
                }

            }
            if (qi.DataType == typeof(DateTime))
            {
                string sdate = "", format = "";
                switch (oper)
                {
                    case "=":
                        if (!ConverToStandard(value, ref sdate, ref format))
                            return "ERROR";
                        return qi.FieldName + " = str_to_date('" + sdate + "'," + format +")";

                    case "%":
                        return qi.FieldName + " LIKE '" + value + "%'";
                    case "!":
                        return qi.FieldName + " IS NULL";
                    case ">":
                        if (!ConverToStandard(value, ref sdate, ref format))
                            return "ERROR";
                        return qi.FieldName + " > str_to_date('" + sdate + "'," + format +")";
                    case "<":
                        if (!ConverToStandard(value, ref sdate, ref format))
                            return "ERROR";
                        return qi.FieldName + " < str_to_date('" + sdate + "'," + format +")";
                    case ">=":
                        if (!ConverToStandard(value, ref sdate, ref format))
                            return "ERROR";
                        return qi.FieldName + " >= str_to_date('" + sdate + "'," + format +")";
                    case "<=":
                        if (!ConverToStandard(value, ref sdate, ref format))
                            return "ERROR";
                        return qi.FieldName + " <= str_to_date('" + sdate + "'," + format +")";
                    case "!=":
                        if (!ConverToStandard(value, ref sdate, ref format))
                            return "ERROR";
                        return qi.FieldName + " != str_to_date('" + sdate + "'," + format +")";
                    case "..":
                        {
                            string[] s1 = qi.Value.Split("..");
                            string ss2 = "", format2="";
                            if (s1.Length >= 2)
                            {
                                if (!ConverToStandard(s1[0], ref sdate, ref format))
                                    return "ERROR";
                                if (!ConverToStandard(s1[1], ref ss2, ref format2))
                                    return "ERROR";
                               
                                return qi.FieldName + " BETWEEN str_to_date('" + sdate + "'," + format + ")" + " AND " + "str_to_date('" + ss2 + "'," + format2 + ")";
                            }
                            else
                                return "ERROR";
                        }
                    case ";":
                        {
                            string[] s2 = qi.Value.Split(";");
                            string where = qi.FieldName + " IN (";
                            foreach (string s in s2)
                            {
                                where += s + ",";
                            }
                            where = where.Remove(where.Length - 1);
                            where += ")";
                            return where;
                        }
                }

            }
            return "ERROR";
        }
        private bool ConverToStandard(string date,ref string ldate,ref string format)
        {
            string sdate = date.Trim();
            string[] s1 = sdate.Split(" ");
            string d = "", t="";
            if (s1.Length >= 2)
            {
                d = s1[0];
                t = s1[1];
            }
            else
            {
                d = sdate;
            }
            if (!string.IsNullOrEmpty(d))
            {
                string[] s2 = d.Split('-');
                if (s2.Length != 3)
                    return false;
                d = s2[0] + "-" ;
                d = d +  (s2[1].Length == 1 ? s2[1].PadLeft(2,'0') : s2[1]);
                d += "-";
                d = d + (s2[2].Length == 1 ? s2[2].PadLeft(2, '0') : s2[2]);
            }
            if (!string.IsNullOrEmpty(t))
            {
                string[] s2 = t.Split(':');
                if (s2.Length > 3)
                    return false;
                else
                    t = t + ":00:00";
                if (s2.Length == 1)
                    t = s2[0] + ":00:00";
                if (s2.Length == 2)
                    t = s2[0] + ":" + s2[1] + ":00";
                if (s2.Length == 3)
                    t = s2[0] + ":" + s2[1] + ":" + s2[2];
            }
            if (string.IsNullOrEmpty(t))
            {
                ldate = d;
                format = "'%Y-%m-%d'";
            }
            else
            {
                ldate = d + " " + t;
                format = "'%Y-%m-%d %H:%i:%s'";
            }
            return true;
        }
        //public string ConditionBuilder()
        //{
        //    return ConditionBuilder(QueryItems);
        //}


        public IList<QueryItem> QueryItems
        {
            get;
            set;
        }


    }
}
